﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using System.Threading;

namespace Spider
{
    public class MySqlDBUtil
    {
        MySqlConfig config;
        private static String connectionString;
        private static string sync = "";
        private static MySqlConnection mySqlConnection = null;
        public static string ServerName = "127.0.0.1";
        public static string UserID = "root";
        public static string Password = "123456";
        public static string DataBaseName = "mytime";
        public static string TableName = "mytime";
        string[] s = new string[] { "Website", "City", "Neighborhood", "Name", "Address", "PhoneNumber", "TypeofPlace", "Special", "Time", "OtherEvents", "OtherTime" };
        public static string URLField = "Website";
        public MySqlDBUtil(MySqlConfig mySqlConfig)
        {
            try
            {
                string temp = "";
                for (int i = 0; i < s.Length; i++)
                {
                    temp = temp + s[i] + " LONGTEXT,";
                }
                temp = temp.Substring(0, temp.Length - 1);
                this.config = mySqlConfig;
                connectionString = "server=" + config.ServerName + ";user id=" + config.UserID + "; password=" + config.Password + ";pooling=false;charset=" + config.CharSet;
                string myExecuteQuery = "create database if not exists " + config.DataBaseName + ";use " + config.DataBaseName + ";create table if not exists " + config.TableName + "(id int not null primary key auto_increment," + temp + ");";
                mySqlConnection = new MySqlConnection(connectionString);
                MySqlCommand myCommand = new MySqlCommand(myExecuteQuery, mySqlConnection);
                if (myCommand.Connection.State == ConnectionState.Closed)
                {
                    myCommand.Connection.Open();
                }

                myCommand.ExecuteNonQuery();
                //if (myCommand.Connection.State == ConnectionState.Open)
                //{
                //    mySqlConnection.Close();
                //}
            }
            catch (System.Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }


        }

        public void close()
        {
            mySqlConnection.Close();
        }

        public void dbOperation(Product p)
        {
            insert(p);
        }
        public void insert(Product p)
        {
            try
            {
                string fields = "";
                string values = "";

                MySqlParameter[] parameters = new MySqlParameter[s.Length];
                for (int i = 0; i < s.Length; i++)
                {
                    parameters[i] = new MySqlParameter("?" + s[i], MySqlDbType.Text);
                    if (p.Fields[i] == null)
                    {
                        p.Fields[i] = "";
                    }
                    parameters[i].Value = p.Fields[i].Replace("\"\"", string.Empty);
                    fields = fields + s[i] + ",";
                    values = values + "?" + s[i] + ",";
                }
                fields = fields.Substring(0, fields.Length - 1);
                values = values.Substring(0, values.Length - 1);
                string sql = "use " + config.DataBaseName + ";insert into " + config.TableName + "(" + fields + ") select " + values + " from dual where not exists(select * from " + config.TableName + " where " + config.TableName + "." + s[0] + "='" + p.URL.Trim() + "');";
                ExecuteInsert(sql, parameters);
            }
            catch (System.Exception e)
            {
                System.Console.WriteLine(e.Message);
            }

        }
        //执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
        public static void ExecuteInsert(string sql, MySqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            Monitor.Enter(sync);
            if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Closed)
            {
                mySqlConnection.Open();
            }
            MySqlCommand cmd = new MySqlCommand(sql, mySqlConnection);
            try
            {
                if (parameters != null) cmd.Parameters.AddRange(parameters);
                cmd.ExecuteNonQuery();
                cmd.Dispose();
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }
            finally
            {
                if (mySqlConnection != null && mySqlConnection.State == ConnectionState.Open)
                {
                    mySqlConnection.Close();
                }
                Monitor.Pulse(sync);
                Monitor.Exit(sync);
            }

        }
        public void ExecuteInsert(string sql)
        {
            ExecuteInsert(sql, null);
        }
        public static bool IsExist(string sql)
        {
            string connectionString = "server=127.0.0.1;user id=root; password=123456;database=mytime;pooling=false;";
            MySqlConnection conn = new MySqlConnection(connectionString);

            if (conn != null && conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            MySqlCommand cmd = new MySqlCommand(sql, conn);
            try
            {

                MySqlDataReader dataReader = cmd.ExecuteReader();
                return dataReader.HasRows;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
                throw e;
            }
            finally
            {
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }

        }
    }
}
